package gov.nhso.prov.acc.apps.acc6;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseTransferBean;
import gov.nhso.prov.acc.beans.Acc6ProvinceAccountBean;
import gov.nhso.prov.acc.beans.Acc6VenderBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;
import com.itextpdf.text.Chunk;
import com.itextpdf.text.Document;
import com.itextpdf.text.Font;
import com.itextpdf.text.Image;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfWriter;

@SuppressWarnings({"rawtypes","deprecation"})
public class ACC60303 extends Utility{
	public String url = "/apps/acc6/ACC60303A.jsp";
	public String path = "";

	public ACC60303(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		}else if(job.equals("search")){
			this.getLastRequest(request, "lastSearch");
			this.search(request);
		}else if(job.equals("add")){
			this.add(request);
		}else if(job.equals("save")){
			this.save(request);
		}else if(job.equals("viewEdit")){
			this.viewEdit(request);
		}else if(job.equals("edit")){
			this.edit(request);
		}else if(job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		}else if(job.equals("confirm")){
			this.confirm(request);
		}else if(job.equals("view")){
			this.view(request);
		}else if(job.equals("del")){
			this.del(request);
		}else if(job.equals("print")){
			this.print(request, response);
		}else if(job.equals("print2")){
			this.print2(request, response);
		}else if(job.equals("getVenderByProvinceId")){
			this.getVenderByProvinceId(request);
		}
		
	}
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("docNo", "");
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String docNo = this.getAttr(request, "docNo");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String expenseStatus = this.getAttr(request, "expenseStatus");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String condition = "";
			this.beanUtil().copyRequest2Bean(request, pageBean);
			expenseBean.setDocNo(docNo);
			expenseBean.setActive("Y");
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseType("A");
			expenseBean.setExpenseStatus(expenseStatus);
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setBudgetYear(budgetYear);
			if (startDate != null && !startDate.equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}
			if (endDate != null && !endDate.equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			if (this.user(request).getAreaLevel().equals("2")){	
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			pageBean = dao.selectPage(expenseBean, " " + condition,pageBean);
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			request.setAttribute("docNo", docNo);
			request.setAttribute("job", "search");
			request.setAttribute("expenseStatusSel", expenseStatus);
			request.setAttribute("expenseSubject", expenseSubject);
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60303A.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String add(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List listCategory = null;

			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			listCategory = dao.selectList(expenseCategoryBean);
			
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(this.dateUtil().getBudgetYear()));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("docNo", "สร้างจากระบบ");
			request.setAttribute("job", "save");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60303B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			int seq = 0;
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseDate = this.getAttr(request, "expenseDate");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"A");
			
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setExpenseDesc(expenseDesc);
			expenseBean.setExpenseType("A");
			expenseBean.setCreatedBy(this.user(request).getUserId());
			expenseBean.setUpdatedBy(this.user(request).getUserId());
			dao.insert(expenseBean);
			
			String refNo = this.getAttr(request, "refNo");
			String allocateCode = this.getAttr(request, "allocateCode");
			String categoryId = this.getAttr(request, "categoryId");
			String responsibleName = this.getAttr(request, "responsibleName");
			String[] transferAmount = request.getParameterValues("transferAmount");
			String[] transferDesc = request.getParameterValues("transferDesc");
			String[] venderCode = request.getParameterValues("venderCode");
			
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setRefNo(refNo);
			expenseTransferBean.setAllocateCode(allocateCode);
			expenseTransferBean.setCategoryId(categoryId);
			expenseTransferBean.setResponsibleName(responsibleName);
			for(int i=0;i<transferAmount.length;i++){
				if(!transferAmount[i].equals("") && transferAmount[i] != null){
					expenseTransferBean.setTransferSeq(String.valueOf(seq+1));
					expenseTransferBean.setTransferAmount(transferAmount[i].replace(",", ""));
					expenseTransferBean.setVenderCode(venderCode[i]);
					expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
					expenseTransferBean.setCreatedBy(this.user(request).getUserId());
					expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
					dao.insert(expenseTransferBean);
					seq++;
				}
			}
			
			request.setAttribute("docNo", docNo);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			this.message(request).setMsgCode("I002");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String viewEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List listCategory = new ArrayList();
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");

			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			listCategory = dao.selectList(expenseCategoryBean);

			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			String venderCode = "";
			Map pv = new HashMap();
			expenseTransferBean.setDocNo(docNo);
			listExpenseTransfer = dao.selectList(expenseTransferBean," ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				venderCode = bean.getVenderCode();
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+venderCode+"'");
				pv = dao.findDataSql(sb.toString());
				
				if (pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				}else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if (pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				}else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				
				if(i==0){
					request.setAttribute("refNo", bean.getRefNo());
					request.setAttribute("allocateCode", bean.getAllocateCode());
					request.setAttribute("categoryId", bean.getCategoryId());
					request.setAttribute("responsibleName", bean.getResponsibleName());
				}
			}
			
			Acc6VenderBean venderBean = new Acc6VenderBean();
			List listVender = new ArrayList();

			venderBean.setProvinceId(provinceId);
			venderBean.setVenderType("A");
			listVender = dao.selectList(venderBean);

			request.setAttribute("listVender", listVender);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(budgetYear));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "edit");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60303B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String edit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseDate = this.getAttr(request, "expenseDate");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String docNo = this.getAttr(request, "docNo");
			int seq = 0;
			
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setExpenseDesc(expenseDesc);
			expenseBean.setExpenseType("A");
			expenseBean.setUpdatedBy(this.user(request).getUserId());
			expenseBean.setUpdatedDate(this.sysDate);
			dao.update(expenseBean);

			String sql = "DELETE "+this.schema+".ACC6_EXPENSE_TRANSFER WHERE DOC_NO='"+docNo+"'";
			dao.execDataSql(sql);
			
			String refNo = this.getAttr(request, "refNo");
			String allocateCode = this.getAttr(request, "allocateCode");
			String categoryId = this.getAttr(request, "categoryId");
			String responsibleName = this.getAttr(request, "responsibleName");
			String[] transferAmount = request.getParameterValues("transferAmount");
			String[] transferDesc = request.getParameterValues("transferDesc");
			String[] venderCode = request.getParameterValues("venderCode");
			
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setRefNo(refNo);
			expenseTransferBean.setAllocateCode(allocateCode);
			expenseTransferBean.setCategoryId(categoryId);
			expenseTransferBean.setResponsibleName(responsibleName);
			for(int i=0;i<transferAmount.length;i++){
				if(!transferAmount[i].equals("") && transferAmount[i] != null){
					expenseTransferBean.setTransferSeq(String.valueOf(seq+1));
					expenseTransferBean.setTransferAmount(transferAmount[i].replace(",", ""));
					expenseTransferBean.setVenderCode(venderCode[i]);
					expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
					expenseTransferBean.setCreatedBy(this.user(request).getUserId());
					expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
					dao.insert(expenseTransferBean);
					seq++;
				}
			}
			
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("docNo", docNo);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String confirm(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String refNo = this.getAttr(request, "refNo");
			String allocateCode = this.getAttr(request, "allocateCode");
			String categoryId = this.getAttr(request, "categoryId");
			String responsibleName = this.getAttr(request, "responsibleName");
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			int seq = 0;
			if(docNo.equals("สร้างจากระบบ")){
				docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"A");
				String expenseDate = this.getAttr(request, "expenseDate");
				String expenseSubject = this.getAttr(request, "expenseSubject");
				String expenseDesc = this.getAttr(request, "expenseDesc");
				
				expenseBean = new Acc6ExpenseBean();
				expenseBean.setDocNo(docNo);
				expenseBean.setExpenseStatus("A");
				expenseBean.setExpenseType("A");
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
				expenseBean.setProvinceId(provinceId);
				expenseBean.setExpenseSubject(expenseSubject);
				expenseBean.setExpenseDesc(expenseDesc);
				expenseBean.setCreatedBy(this.user(request).getUserId());
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				dao.insert(expenseBean);

				String[] transferAmount = request.getParameterValues("transferAmount");
				String[] transferDesc = request.getParameterValues("transferDesc");
				String[] venderCode = request.getParameterValues("venderCode");
				
				expenseTransferBean.setDocNo(docNo);
				expenseTransferBean.setRefNo(refNo);
				expenseTransferBean.setAllocateCode(allocateCode);
				expenseTransferBean.setCategoryId(categoryId);
				expenseTransferBean.setResponsibleName(responsibleName);
				for(int i=0;i<transferAmount.length;i++){
					if(!transferAmount[i].equals("") && transferAmount[i] != null){
						expenseTransferBean.setTransferSeq(String.valueOf(seq+1));
						expenseTransferBean.setTransferAmount(transferAmount[i].replace(",", ""));
						expenseTransferBean.setVenderCode(venderCode[i]);
						expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
						expenseTransferBean.setCreatedBy(this.user(request).getUserId());
						expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
						dao.insert(expenseTransferBean);
						seq++;
					}
				}
			}else{
				expenseBean.setDocNo(docNo);
				expenseBean.setProvinceId(provinceId);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setExpenseStatus("A");
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				expenseBean.setUpdatedDate(this.sysDate);
				dao.update(expenseBean);
				
				String sql = "DELETE "+this.schema+".ACC6_EXPENSE_TRANSFER WHERE DOC_NO='"+docNo+"'";
				dao.execDataSql(sql);
				
				String[] transferAmount = request.getParameterValues("transferAmount");
				String[] transferDesc = request.getParameterValues("transferDesc");
				String[] venderCode = request.getParameterValues("venderCode");
				
				expenseTransferBean.setDocNo(docNo);
				expenseTransferBean.setRefNo(refNo);
				expenseTransferBean.setAllocateCode(allocateCode);
				expenseTransferBean.setCategoryId(categoryId);
				expenseTransferBean.setResponsibleName(responsibleName);
				for(int i=0;i<transferAmount.length;i++){
					if(!transferAmount[i].equals("") && transferAmount[i] != null){
						expenseTransferBean.setTransferSeq(String.valueOf(seq+1));
						expenseTransferBean.setTransferAmount(transferAmount[i].replace(",", ""));
						expenseTransferBean.setVenderCode(venderCode[i]);
						expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
						expenseTransferBean.setCreatedBy(this.user(request).getUserId());
						expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
						dao.insert(expenseTransferBean);
						seq++;
					}
				}
				
			}
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean = new Acc6ExpenseTransferBean();
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setActive("Y");
			listExpenseTransfer = dao.selectList(expenseTransferBean," ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());
				bean.setVenderName(pv.get("VENDER_NAME").toString());
				bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				if(i ==0){
					request.setAttribute("refNo", bean.getRefNo());
					request.setAttribute("allocateName", this.dataUtil(dao).getAllocateById(bean.getAllocateCode()));
					request.setAttribute("categoryName", this.dataUtil(dao).getCategoryById(bean.getCategoryId()));
					request.setAttribute("responsibleName", bean.getResponsibleName());
				}
			}

			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60303C.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setActive("Y");
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			listExpenseTransfer = dao.selectList(expenseTransferBean, xcols," ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB, VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());
				
				if(pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				} else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if(pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				} else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				
				if(i ==0){
					request.setAttribute("refNo", bean.getRefNo());
					request.setAttribute("allocateName", this.dataUtil(dao).getAllocateById(bean.getAllocateCode()));
					request.setAttribute("categoryName", this.dataUtil(dao).getCategoryById(bean.getCategoryId()));
					request.setAttribute("responsibleName", bean.getResponsibleName());
				}
				
			}

			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60303C.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String del(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String docNo = this.getAttr(request, "docNo");
			
			dao.execDataSql("UPDATE ACC6_EXPENSE_TRANSFER SET ACTIVE = 'N' WHERE DOC_NO = '"+docNo+"'");
			dao.execDataSql("UPDATE ACC6_EXPENSE SET ACTIVE = 'N' WHERE DOC_NO = '"+docNo+"'");
			
			request.setAttribute("docNo", "");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	
	}
	private void print(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		File fontFile = new File("THSarabun.ttf");
	    Paragraph preface = new Paragraph();
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			ByteArrayOutputStream outputStream = null;

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
		    Document document = new Document();
		    StringBuffer sb = new StringBuffer();
		    Map map = new HashMap();
		    Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
		    expenseBean.setDocNo(docNo);
		    expenseBean.setBudgetYear(budgetYear);
		    expenseBean = (Acc6ExpenseBean)dao.select(expenseBean);
		    
		    outputStream = new ByteArrayOutputStream();
		    PdfWriter.getInstance(document, outputStream);
		    Paragraph paragraph = new Paragraph();
		    document.setMargins(65, 50, 130, 0);
		    document.open();
		    BaseFont baseFont = null;
			baseFont = BaseFont.createFont(fontFile.toString() , BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
		    Font font = new Font(baseFont, 16);
		    String ws = "\u00a0\u00a0";
		    String tab = ws+ws+ws+ws;
			String expenseDate = "";
			this.path = request.getRealPath("WEB-INF/classes/krut.jpeg");
		    Image krut = Image.getInstance(this.path);
		    krut.setAbsolutePosition(250f, 720f);
		    krut.scaleAbsolute(90f, 90f);
		    document.add(krut);
		    paragraph.setFont(font);
		    paragraph.add("ที่");
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add("สำนักงานสาธารณสุขจังหวัด"+ws+""+this.user(request).getAreaCodeName());
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(this.dataUtil(dao).getProvinceAddressById(provinceId));
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab+ws);
		    expenseDate = this.dateUtil().convertDate("DP", expenseBean.getExpenseDate());
		    paragraph.add("วันที่ "+this.thaiNumber(this.dateUtil().changeFormatFullDate(expenseDate, "/", " ")));
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("เรื่อง   แจ้งโอนเงิน");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("เรียน  	ผู้จัดการธนาคารเพื่อการเกษตรและสหกรณ์การเกษตร สาขา");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("สิ่งที่ส่งมาด้วย	รายละเอียดประกอบการโอนเงิน");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab);
		    
		    sb.append("SELECT  COUNT(DISTINCT VENDER_CODE) AS COUNT_VENDER ,SUM(TRANSFER_AMOUNT) AS SUM_TRANSFER_AMOUNT ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER ");
		    sb.append("WHERE DOC_NO = '"+docNo+"'");
		    map = dao.findDataSql(sb.toString());
		    
		    paragraph.add("ด้วยสำนักงานสาธารณสุขจังหวัด " + this.user(request).getAreaCodeName()+" มีความประสงค์จะโอนเงินเข้าบัญชีให้กับหน่วย บริการ/คู่สัญญา  จำนวน "+this.thaiNumber(map.get("COUNT_VENDER").toString()));
		    paragraph.add(" หน่วย 	จำนวนเงิน  ("+this.thaiNumber(this.digitUtil().numberFormat("#,##0.00", map.get("SUM_TRANSFER_AMOUNT").toString()))+") บาท  ("+this.bathText().bathText(map.get("SUM_TRANSFER_AMOUNT").toString())+")");
		    paragraph.add(" โดย ");
		    paragraph.add("ตัดจ่ายจากบัญชี\"เงินกองทุนหลักประกันสุขภาพแห่งชาติ สาขาจังหวัด " + this.user(request).getAreaCodeName());
		    
		    Acc6ProvinceAccountBean provinceAccountBean = new Acc6ProvinceAccountBean();
		    provinceAccountBean.setProvinceId(provinceId);
		    provinceAccountBean = (Acc6ProvinceAccountBean)dao.select(provinceAccountBean);
		    
		    paragraph.add("\" (บัญชี ๖)  เลขที่บัญชี  "+this.thaiNumber(provinceAccountBean.getProvinceAccountSaving()));
		    paragraph.add(" รายละเอียดตามเอกสารที่ส่งมาพร้อมนี้");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab);
		    paragraph.add("จึงเรียนมาเพื่อโปรดพิจารณาและดำเนินการต่อไปด้วย จะเป็นพระคุณ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab+ws);
		    paragraph.add("ขอแสดงความนับถือ");
		    
			preface.add(paragraph);
			document.add(preface);
		    document.newPage();
		    document.close();
			
			ByteArrayOutputStream baos = outputStream;
			response.setHeader("Expires", "0"); 
	        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); 
	        response.setHeader("Pragma", "public"); 
	        response.setContentType("application/pdf");
	        response.setHeader("Content-Disposition", "attachment; filename="+docNo+".pdf");
			response.setCharacterEncoding("windows-874");
	        response.setContentLength(baos.size()); 
	        ServletOutputStream out;
			
			out = response. getOutputStream();
	        baos.writeTo(out); 
	        out.flush(); 
	        out.close(); 
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void print2(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		File fontFile = new File("THSarabun.ttf");
	    Paragraph preface = new Paragraph();
	    String buffer = "";
	    String[] strArr = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			ByteArrayOutputStream outputStream = null;

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
		    Document document = new Document(PageSize.A4, (float) 84.96, (float) 22.32, (float) 70.56, 0);
		    StringBuffer sb = new StringBuffer();
		    String str = "";
		    Map map = new HashMap();
		    List list = new ArrayList();

		    outputStream = new ByteArrayOutputStream();
		    PdfWriter.getInstance(document, outputStream);
		    Paragraph paragraph = new Paragraph();
		    //document.setMargins(65, 50, 105, 0);
		    document.open();
		    BaseFont baseFont = null;
			baseFont = BaseFont.createFont(fontFile.toString() , BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
		    Font font = new Font(baseFont, 16);
		    Font headFont = new Font(baseFont, 18);
		    String ws = "\u00a0\u00a0";
		    String tab = ws+ws+ws+ws;
			String expenseDate = "";
			String expenseDateMinus = "";

		    sb.delete(0, sb.length());
		    sb.append("SELECT  COUNT(DISTINCT AET.VENDER_CODE) AS COUNT_VENDER, ");
		    sb.append("SUM(AET.TRANSFER_AMOUNT) AS SUM_TRANSFER_AMOUNT, ");
		    sb.append("AE.EXPENSE_DATE -1 AS EXPENSE_DATE_MINUS,AE.EXPENSE_DATE,AE.EXPENSE_DATE + 1 AS EXPENSE_DATE_PLUS ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER AET,ACC6_EXPENSE AE ");
		    sb.append("WHERE AET.DOC_NO = '"+docNo+"' AND AE.DOC_NO = AET.DOC_NO GROUP BY AE.EXPENSE_DATE");
		    map = dao.findDataSql(sb.toString());
		    
		    String sumTransferAmount = "";
		    if(map.get("SUM_TRANSFER_AMOUNT").toString()== null || map.get("SUM_TRANSFER_AMOUNT").toString().equals("")){
		    	sumTransferAmount = "0";
		    }else{
		    	sumTransferAmount = map.get("SUM_TRANSFER_AMOUNT").toString();
		    }

			this.path = request.getRealPath("WEB-INF/classes/krut.jpeg");
		    Image krut = Image.getInstance(this.path);
		    krut.setAbsolutePosition(76f, 750f);
		    krut.scaleAbsolute(48f, 48f);
		    document.add(krut);
		    paragraph.setFont(headFont);
		    paragraph.add(tab+tab+tab+tab+tab+tab+ws+ws);
		    paragraph.add("บันทึกข้อความ");
		    paragraph.setFont(font);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("ส่วนราชการ "+ws+ws+ws+tab+tab+tab+tab);
		    paragraph.add("สำนักงานสาธารณสุขจังหวัด  "+ this.user(request).getAreaCodeName());
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("ที่");
		    paragraph.add(tab+tab+tab+tab+tab+ws+ws+ws+ws+ws+ws);
		    expenseDate = this.dateUtil().changeFormatFullDate(this.dateUtil().convertDate("DP", map.get("EXPENSE_DATE").toString()), "/", " ");
		    expenseDateMinus = this.dateUtil().changeFormatFullDate(this.dateUtil().convertDate("DP", map.get("EXPENSE_DATE_MINUS").toString()), "/", " ");
		    paragraph.add("วันที่ " + this.thaiNumber(expenseDate));
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT DISTINCT AC.ALLOCATE_NAME ");
		    sb.append("FROM ACC6_ALLOCATE AC,ACC6_EXPENSE_TRANSFER AET ");
		    sb.append("WHERE AET.DOC_NO = '"+docNo+"' AND AET.ALLOCATE_CODE = AC.ALLOCATE_CODE AND AC.ACTIVE = 'Y'");
		    list = dao.listDataSql(sb.toString());
		    
		    for(int i=0;i<list.size();i++){
		    	map = (Map)list.get(i);
		    	str += map.get("ALLOCATE_NAME").toString()+" , ";
		    }
		    str = str.substring(0,str.length()-2);
		    
		    paragraph.add("เรื่อง   	ขออนุมัติโอนเงิน "+str);
		    paragraph.add( "ปีงบประมาณ พ.ศ. "+this.thaiNumber(budgetYear));
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("_________________________________________________________________________________");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("เรียน  	นายแพทย์สาธารณสุขจังหวัด " + this.user(request).getAreaCodeName());
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("สิ่งที่ส่งมาด้วย	ข้อมูลรายละเอียดการโอนเงินให้หน่วยบริการ/คู่สัญญา");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab);
		    paragraph.add("ด้วยสำนักงานหลักประกันสุขภาพแห่งชาติได้จัดสรรเงินและโอนเงินกองทุน " + str);
		    paragraph.add(" ปีงบประมาณ พ.ศ. " + this.thaiNumber(budgetYear));
		    paragraph.add(" ให้สาธารณสุขจังหวัด "+ this.user(request).getAreaCodeName());
		    paragraph.add("จ่ายเป็นเงินสนับสนุน และส่งเสริมการจัดบริการสาธารณสุขของหน่วยบริการ รวมทั้งการพัฒนาระบบบริการและบุคลากร โดยมีเงินกองทุน "+str);
		    paragraph.add(" คงเหลือ ณ วันที "+this.thaiNumber(expenseDateMinus));
		    paragraph.add(" เป็นจำนวนเงิน " + this.thaiNumber(this.digitUtil().numberFormat("#,##0.00", sumTransferAmount))+" บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab);
		    paragraph.add("กลุ่มงาน");
		    paragraph.add(tab+tab+tab+tab);
		    paragraph.add("ขอโอนเงินกองทุน "+str);
		    paragraph.add("จากบัญชี \"เงินกองทุนหลักประกันสุขภาพแห่งชาติ สาขาจังหวัด "+ this.user(request).getAreaCodeName());
		    
		    Acc6ProvinceAccountBean provinceAccountBean = new Acc6ProvinceAccountBean();
		    provinceAccountBean.setProvinceId(provinceId);
		    provinceAccountBean = (Acc6ProvinceAccountBean)dao.select(provinceAccountBean);
		    
		    paragraph.add("\"(บัญชี  ๖)  เลขที่บัญชี  " + this.thaiNumber(provinceAccountBean.getProvinceAccountSaving()));
		    paragraph.add(" ให้หน่วยบริการ/คู่สัญญา ตามสิ่งที่ส่งมาด้วยเป็นจำนวนเงิน "+ this.thaiNumber(this.digitUtil().numberFormat("#,##0.00", sumTransferAmount)));
		    paragraph.add("บาท ("+ this.bathText().bathText(sumTransferAmount)+")");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab);
		    paragraph.add("จึงเรียนมาเพื่อโปรดพิจารณาลงนามอนุมัติ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("สถานะการเงิน ณ วันที่ "+this.thaiNumber(expenseDateMinus));
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("เงินบัญชี ๖ สสจ. "+ this.user(request).getAreaCodeName());
		    paragraph.add(Chunk.NEWLINE);
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT SUM(INCOME_AMOUNT) - SUM(EXPENSE_AMOUNT) AS RESULTS ");
		    sb.append("FROM ACC6_TRANSACTION ");
		    sb.append("WHERE BUDGET_YEAR = '"+budgetYear+"' AND PROVINCE_ID = '" + provinceId +"' AND REASON_CODE = '00'");
		    map = dao.findDataSql(sb.toString());
		    
		    String result = map.get("RESULTS").toString();
		    paragraph.add("เงินในบัญชี   " + this.thaiNumber(this.digitUtil().numberFormat("#,##0.00",map.get("RESULTS").toString()))+" บาท ");
		    paragraph.add(Chunk.NEWLINE);

		    sb.delete(0, sb.length());
		    sb.append("SELECT SUM(TRANSFER_AMOUNT) AS RESULTS ,MAX(UPDATED_DATE) AS UPDATED_DATE ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER ");
		    sb.append("WHERE DOC_NO = '"+docNo+"'");
		    map = dao.findDataSql(sb.toString());
		    
		    paragraph.add("เบิกจ่าย        " +this.thaiNumber(this.digitUtil().numberFormat("#,##0.00",map.get("RESULTS").toString()))+ " บาท ");
		    paragraph.add(Chunk.NEWLINE);

		    if(map.get("UPDATED_DATE") != null && !map.get("UPDATED_DATE").toString().equals("")){
		    	strArr = map.get("UPDATED_DATE").toString().split("\\ ");
		    	buffer = strArr[0];
		    }
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT SUM(TRANSFER_AMOUNT) AS SUM_TRANSFER ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER ");
		    sb.append("WHERE DOC_NO = '"+docNo+"' AND TO_DATE('"+buffer+"','YYYY-MM-DD hh24:mi:ss') = TO_DATE(UPDATED_DATE)");
		    map = dao.findDataSql(sb.toString());
		    
		    double sumTtansfer = 0d;
		    if(map.get("SUM_TRANSFER").toString() != null && !map.get("SUM_TRANSFER").toString().equals("")){
		    	sumTtansfer = Double.parseDouble(map.get("SUM_TRANSFER").toString());
		    }
		    
		    double remain = Double.parseDouble(result) - sumTtansfer;
		    paragraph.add("เบิกจ่ายสะสม "+this.thaiNumber(this.digitUtil().numberFormat("#,##0.00",map.get("SUM_TRANSFER").toString()))+" บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("คงเหลือ "+this.thaiNumber(this.digitUtil().numberFormat("#,##0.00",String.valueOf(remain)))+ " บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab+tab+ws+ws);
		    paragraph.add("ตรวจสอบถูกต้อง");
		    
			preface.add(paragraph);
			document.add(preface);
		    document.newPage();
		    document.close();
			
			ByteArrayOutputStream baos = outputStream;
			response.setHeader("Expires", "0"); 
	        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); 
	        response.setHeader("Pragma", "public"); 
	        response.setContentType("application/pdf"); 
	        response.setHeader("Content-Disposition", "attachment; filename="+docNo+".pdf");
	        response.setContentLength(baos.size()); 
	        ServletOutputStream out;
			
			out = response. getOutputStream();
	        baos.writeTo(out); 
	        out.flush(); 
	        out.close(); 
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	private String getVenderByProvinceId(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String provinceId = this.getAttr(request, "provinceId");
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			Acc6VenderBean venderBean = new Acc6VenderBean();
			List listCategory = null;
			List listVender = null;

			expenseCategoryBean.setCategoryAcc("6");
			listCategory = dao.selectList(expenseCategoryBean);
			
			venderBean.setProvinceId(provinceId);
			venderBean.setVenderType("A");
			listVender = dao.selectList(venderBean);
			
			if (listVender.size() == 0){
				this.message(request).setMsg("I", "ไม่พบข้อมูล สสอ. ในจังหวัด " + this.dataUtil(dao).getProvinceName(provinceId));
			}
			
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("listVender", listVender);
			request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(this.dateUtil().getBudgetYear()));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("docNo", "สร้างจากระบบ");
			request.setAttribute("job", "save");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60303B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
